# frozen_string_literal: true

class UpdateUnsetHasMergeRequestOnVulnerabilityReadsFunction < Gitlab::Database::Migration[2.3]
  milestone '18.7'

  def up
    execute <<~SQL
      CREATE OR REPLACE FUNCTION unset_has_merge_request_on_vulnerability_reads() RETURNS trigger
        LANGUAGE plpgsql
        AS $$
      DECLARE
        has_merge_request_links integer;
      BEGIN
        IF (SELECT current_setting('vulnerability_management.dont_execute_db_trigger', true) = 'true') THEN
          RETURN NULL;
        END IF;

        PERFORM 1
        FROM
          vulnerability_reads
        WHERE
          vulnerability_id = OLD.vulnerability_id
        FOR UPDATE;

        SELECT 1 INTO has_merge_request_links FROM vulnerability_merge_request_links WHERE vulnerability_id = OLD.vulnerability_id LIMIT 1;

        IF (has_merge_request_links = 1) THEN
          RETURN NULL;
        END IF;

        UPDATE
          vulnerability_reads
        SET
          has_merge_request = false
        WHERE
          vulnerability_id = OLD.vulnerability_id;

        RETURN NULL;
      END
      $$;
    SQL
  end

  def down
    execute <<~SQL
      CREATE OR REPLACE FUNCTION unset_has_merge_request_on_vulnerability_reads() RETURNS trigger
        LANGUAGE plpgsql
        AS $$
      DECLARE
        has_merge_request_links integer;
      BEGIN
        PERFORM 1
        FROM
          vulnerability_reads
        WHERE
          vulnerability_id = OLD.vulnerability_id
        FOR UPDATE;

        SELECT 1 INTO has_merge_request_links FROM vulnerability_merge_request_links WHERE vulnerability_id = OLD.vulnerability_id LIMIT 1;

        IF (has_merge_request_links = 1) THEN
          RETURN NULL;
        END IF;

        UPDATE
          vulnerability_reads
        SET
          has_merge_request = false
        WHERE
          vulnerability_id = OLD.vulnerability_id;

        RETURN NULL;
      END
      $$;
    SQL
  end
end
